namespace TSL.DAL
{
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using TSL.Model;

    public class sys_bank
    {
        public void Addback(TSL.Model.sys_bank model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Bank_Money_ADD");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "back_money", DbType.AnsiString, model.back_money);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Addsys_bank(TSL.Model.sys_bank model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_sys_bank_ADD");
            db.AddInParameter(dbCommand, "id", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "type", DbType.AnsiString, model.type);
            db.AddInParameter(dbCommand, "back_money", DbType.AnsiString, model.back_money);
            db.AddInParameter(dbCommand, "back_money2", DbType.AnsiString, model.back_money2);
            db.AddInParameter(dbCommand, "b_befor", DbType.AnsiString, model.b_befor);
            db.AddInParameter(dbCommand, "b_after", DbType.AnsiString, model.b_after);
            db.AddInParameter(dbCommand, "addtime", DbType.AnsiString, model.addtime);
            db.AddInParameter(dbCommand, "prostate", DbType.AnsiString, model.prostate);
            db.AddInParameter(dbCommand, "state", DbType.AnsiString, model.state);
            db.AddInParameter(dbCommand, "lotteryid", DbType.AnsiString, model.lotteryid);
            db.AddInParameter(dbCommand, "follows", DbType.AnsiString, model.follows);
            db.AddInParameter(dbCommand, "expect", DbType.AnsiString, model.expect);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Delete(string ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_sys_bank_Delete");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, ID);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Deletesys_bank(string ID)
        {
            List<string> strList = new List<string>();
            strList.Add("delete from sys_bank where ID = '" + ID + "'");
            DbHelperSQL.ExecuteSqlTran(strList);
        }

        public bool Exists(string state)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from bank_back");
            strSql.Append(" where follows like '%取款%' and  state=@state ");
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@state", SqlDbType.VarChar, 50) };
            parameters[0].Value = state;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public List<TSL.Model.sys_bank> GetListArray(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_sys_bank ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append("order by id desc");
            List<TSL.Model.sys_bank> list = new List<TSL.Model.sys_bank>();
            using (IDataReader dataReader = DatabaseFactory.CreateDatabase().ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind(dataReader));
                }
            }
            return list;
        }

        public List<TSL.Model.sys_bank> GetListArrayPage(string strWhere, int PageNo, int PageSize, out int recordCount)
        {
            StringBuilder strSelectSql = new StringBuilder();
            strSelectSql.Append("SELECT * FROM (");
            strSelectSql.Append("SELECT ROW_NUMBER() OVER (ORDER BY ID)");
            strSelectSql.Append("AS Row,* FROM V_sys_bank");

            StringBuilder strCoutSql = new StringBuilder();
            strCoutSql.Append("SELECT COUNT(*) as recordCount FROM V_sys_bank");

            if (strWhere.Trim() != "")
            {
                strSelectSql.Append(" WHERE " + strWhere);
                strCoutSql.Append(" WHERE " + strWhere);
            }

            strSelectSql.Append(string.Format(")AS TEMP WHERE ROW BETWEEN {0} and {1}", (PageNo * PageSize) - PageSize + 1, PageNo * PageSize));

            List<TSL.Model.sys_bank> list = new List<TSL.Model.sys_bank>();
            recordCount = 0;

            DataSet set = DBUtility.DbHelperSQL.Query(strSelectSql.ToString() + strCoutSql.ToString());

            if (set.Tables.Count == 2)
            {
                recordCount = int.Parse(set.Tables[1].Rows[0]["recordCount"].ToString());
                foreach (DataRow row in set.Tables[0].Rows)
                {
                    list.Add(this.ReaderBind(row));
                }
            }
            return list;
        }

        public List<TSL.Model.sys_bank> GetListArray(string strWhere,string username, string regfrom, string stratetime, string endtime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_sys_bank where 1=1 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }
            strSql.Append("and  username in (select username from users where regfrom like  '%'+ @username + '%' and  username like '%'+ @regfrom + '%')");
            strSql.Append(" and convert(datetime,addtime)>=convert(datetime,@stratetime)");
            strSql.Append(" and convert(datetime,addtime)<=convert(datetime,@endtime)");
            strSql.Append("order by id desc");

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@username", SqlDbType.VarChar, 50), 
                new SqlParameter("@regfrom", SqlDbType.VarChar, 50), 
                new SqlParameter("@stratetime", SqlDbType.VarChar, 50),
                new SqlParameter("@endtime", SqlDbType.VarChar, 50)};
            parameters[0].Value = username;
            parameters[1].Value = regfrom;
            parameters[2].Value = stratetime;
            parameters[3].Value = endtime;

            List<TSL.Model.sys_bank> list = new List<TSL.Model.sys_bank>();
            using (IDataReader dataReader = TSL.DBUtility.DbHelperSQL.ExecuteReader(strSql.ToString(), parameters))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind(dataReader));
                }
            }
            return list;
        }

        public List<TSL.Model.sys_bank> GetListArray(string strWhere, string qihao, string stratetime, string endtime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_sys_bank where 1=1 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }
            strSql.Append(" and expect like '%'+ @qihao + '%'");
            strSql.Append(" and convert(datetime,addtime)>=convert(datetime,@stratetime)");
            strSql.Append(" and convert(datetime,addtime)<=convert(datetime,@endtime)");
            strSql.Append("order by id desc");
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@qihao", SqlDbType.VarChar, 50), 
                new SqlParameter("@stratetime", SqlDbType.VarChar, 50),
                new SqlParameter("@endtime", SqlDbType.VarChar, 50)};
            parameters[0].Value = qihao;
            parameters[1].Value = stratetime;
            parameters[2].Value = endtime;

            List<TSL.Model.sys_bank> list = new List<TSL.Model.sys_bank>();
            using (IDataReader dataReader = TSL.DBUtility.DbHelperSQL.ExecuteReader(strSql.ToString(), parameters))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind(dataReader));
                }
            }
            return list;
        }

        public List<TSL.Model.sys_bank> GetListBank(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_List_bank ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append("order by id desc");
            List<TSL.Model.sys_bank> list = new List<TSL.Model.sys_bank>();
            using (IDataReader dataReader = DatabaseFactory.CreateDatabase().ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind(dataReader));
                }
            }
            return list;
        }

        public DataSet GetListFromView(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_sys_bank ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by id desc");
            return DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public TSL.Model.sys_bank GetModel(string ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_sys_bank_GetModelByID");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, ID);
            TSL.Model.sys_bank model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind(dataReader);
                }
            }
            return model;
        }

        public TSL.Model.sys_bank GetModel(string name, string password)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_sys_bank_GetModel");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, name);
            db.AddInParameter(dbCommand, "password", DbType.AnsiString, password);
            TSL.Model.sys_bank model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind(dataReader);
                }
            }
            return model;
        }

        public TSL.Model.sys_bank ReaderBind(DataRow dataReader)
        {
            TSL.Model.sys_bank model = new TSL.Model.sys_bank();
            model.id = dataReader["id"].ToString();
            model.username = dataReader["username"].ToString();
            model.type = dataReader["type"].ToString();
            model.back_money = Math.Round(Convert.ToDouble(dataReader["back_money"].ToString()), 2).ToString();
            model.back_money2 = Math.Round(Convert.ToDouble(dataReader["back_money2"].ToString()), 2).ToString();
            model.b_befor = Math.Round(Convert.ToDouble(dataReader["b_befor"].ToString()), 2).ToString();
            model.b_after = Math.Round(Convert.ToDouble(dataReader["b_after"].ToString()), 2).ToString();
            model.addtime = dataReader["addtime"].ToString();
            model.prostate = dataReader["prostate"].ToString();
            model.state = dataReader["state"].ToString();
            model.lotteryid = dataReader["lotteryid"].ToString();
            model.follows = dataReader["follows"].ToString();
            model.expect = dataReader["expect"].ToString();
            model.CHS_state = dataReader["CHS_state"].ToString();
            return model;
        }


        public TSL.Model.sys_bank ReaderBind(IDataReader dataReader)
        {
            TSL.Model.sys_bank model = new TSL.Model.sys_bank();
            model.id = dataReader["id"].ToString();
            model.username = dataReader["username"].ToString();
            model.type = dataReader["type"].ToString();
            model.back_money = Math.Round(Convert.ToDouble(dataReader["back_money"].ToString()), 2).ToString();
            model.back_money2 = Math.Round(Convert.ToDouble(dataReader["back_money2"].ToString()), 2).ToString();
            model.b_befor = Math.Round(Convert.ToDouble(dataReader["b_befor"].ToString()), 2).ToString();
            model.b_after = Math.Round(Convert.ToDouble(dataReader["b_after"].ToString()), 2).ToString();
            model.addtime = dataReader["addtime"].ToString();
            model.prostate = dataReader["prostate"].ToString();
            model.state = dataReader["state"].ToString();
            model.lotteryid = dataReader["lotteryid"].ToString();
            model.follows = dataReader["follows"].ToString();
            model.expect = dataReader["expect"].ToString();
            model.CHS_state = dataReader["CHS_state"].ToString();
            return model;
        }

        public void refuse(TSL.Model.sys_bank model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_SY_bank_Refuse");
            db.AddInParameter(dbCommand, "id", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "follows", DbType.AnsiString, model.follows);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Update(TSL.Model.sys_bank model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_sys_bank_Update");
            db.AddInParameter(dbCommand, "id", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "type", DbType.AnsiString, model.type);
            db.AddInParameter(dbCommand, "back_money", DbType.AnsiString, model.back_money);
            db.AddInParameter(dbCommand, "back_money2", DbType.AnsiString, model.back_money2);
            db.AddInParameter(dbCommand, "b_befor", DbType.AnsiString, model.b_befor);
            db.AddInParameter(dbCommand, "b_after", DbType.AnsiString, model.b_after);
            db.AddInParameter(dbCommand, "addtime", DbType.AnsiString, model.addtime);
            db.AddInParameter(dbCommand, "prostate", DbType.AnsiString, model.prostate);
            db.AddInParameter(dbCommand, "state", DbType.AnsiString, model.state);
            db.AddInParameter(dbCommand, "lotteryid", DbType.AnsiString, model.lotteryid);
            db.AddInParameter(dbCommand, "follows", DbType.AnsiString, model.follows);
            db.AddInParameter(dbCommand, "expect", DbType.AnsiString, model.expect);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Update_Bank_Back(TSL.Model.sys_bank model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Update_Bank_Back");
            db.AddInParameter(dbCommand, "id", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "back_money", DbType.AnsiString, model.back_money);
            db.AddInParameter(dbCommand, "back_money2", DbType.AnsiString, model.back_money2);
            db.AddInParameter(dbCommand, "b_after", DbType.AnsiString, model.b_after);
            db.AddInParameter(dbCommand, "b_befor", DbType.AnsiString, model.b_befor);
            db.AddInParameter(dbCommand, "follows", DbType.AnsiString, model.follows);
            db.ExecuteNonQuery(dbCommand);
        }
    }
}

